package com.yc.testupload.controller;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import org.json.JSONArray;
import org.json.JSONObject;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLDataException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.yc.testupload.util.DatabaseUtil;
import com.yc.testupload.DatabaseConnectionTester;

/**
 * 素材库数据接口Servlet
 * 提供从数据库查询素材数据的API
 */
@WebServlet("/api/materials")
public class MaterialServlet extends HttpServlet {
    private static final Logger logger = LogManager.getLogger(MaterialServlet.class);

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)            throws ServletException, IOException {
        response.setContentType("application/json;charset=UTF-8");
        PrintWriter out = response.getWriter();
        JSONObject resultJson = new JSONObject();

        try {
            // 获取请求参数
            String category = request.getParameter("category") != null ? request.getParameter("category") : "";
            
            // 先检查数据库连接状态
            Map<String, Object> connectionTestResult = DatabaseConnectionTester.testConnectionDetailed();
            boolean isConnected = (Boolean) connectionTestResult.getOrDefault("success", false);
            
            if (!isConnected) {
                // 数据库连接失败，返回连接错误信息
                logger.error("数据库连接失败: {}", connectionTestResult.get("message"));
                resultJson.put("code", 500);
                resultJson.put("message", "数据库连接失败: " + connectionTestResult.get("message"));
                if (connectionTestResult.containsKey("suggestion")) {
                    resultJson.put("suggestion", connectionTestResult.get("suggestion"));
                }
                response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
            } else {
                // 从数据库查询素材列表
                List<Map<String, Object>> materials = getMaterials(request, category);

                // 构建响应结果
                resultJson.put("code", 0);
                resultJson.put("message", "success");
                resultJson.put("data", new JSONArray(materials));

                response.setStatus(HttpServletResponse.SC_OK);
            }
        } catch (Exception e) {
            logger.error("获取素材列表失败: {}", e.getMessage(), e);
            resultJson.put("code", 4);
            resultJson.put("message", "服务器内部错误: " + e.getMessage());
            response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
        } finally {
            out.write(resultJson.toString());
            out.flush();
            out.close();
        }
    }

    /**
     * 从数据库查询素材列表
     */
    private List<Map<String, Object>> getMaterials(HttpServletRequest request, String category) {
        List<Map<String, Object>> materials = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT m.id, m.title, m.content, m.category_id, m.user_id, m.source_url, m.add_time, m.edit_time, m.status ");
            sql.append("FROM wk_material m ");
            sql.append("WHERE 1=1 ");

            // 从会话中获取当前登录用户的user_id
            Integer userId = (Integer) request.getSession().getAttribute("userId");
            if (userId == null) {
                logger.warn("会话中未找到有效的用户ID，请先登录");
                // 不设置默认值，直接返回空列表
                logger.info("未登录用户，返回空素材列表");
                return materials;
            }
            logger.info("从会话中获取到用户ID: {}", userId);
            sql.append("AND m.user_id = ? ");

            // 添加分类过滤条件
            if (!category.isEmpty()) {
                sql.append("AND m.category_id = ? ");
            }

            // 添加排序
            sql.append("ORDER BY m.add_time DESC");

            // 完整记录SQL语句及参数值
            logger.info("准备执行查询素材列表SQL: {}", sql.toString());
            logger.info("SQL查询参数 - user_id: {}", userId);
            if (!category.isEmpty()) {
                logger.info("SQL查询参数 - category_id: {}", category);
            }
            
            // 构建带实际参数值的SQL语句（便于调试）
            String sqlWithParams = sql.toString();
            sqlWithParams = sqlWithParams.replaceFirst("\\?", "'" + userId + "'");
            if (!category.isEmpty()) {
                sqlWithParams = sqlWithParams.replaceFirst("\\?", "'" + category + "'");
            }
            logger.info("完整SQL语句（含参数值）: {}", sqlWithParams);
            
            pstmt = conn.prepareStatement(sql.toString());
            int paramIndex = 1;
            pstmt.setInt(paramIndex++, userId);
            
            // 设置分类参数
            if (!category.isEmpty()) {
                pstmt.setString(paramIndex++, category);
            }

            rs = pstmt.executeQuery();
            logger.info("SQL查询执行成功");

            int count = 0;
            while (rs.next()) {
                Map<String, Object> material = new HashMap<>();
                material.put("id", rs.getInt("id"));
                material.put("title", rs.getString("title"));
                material.put("content", rs.getString("content"));
                material.put("categoryId", rs.getInt("category_id"));
                material.put("userId", rs.getInt("user_id"));
                material.put("sourceUrl", rs.getString("source_url"));
                // 处理时间字段 - 数据库中可能存储为LONG类型
                try {
                    // 尝试先作为Timestamp获取
                    material.put("addTime", rs.getTimestamp("add_time"));
                    material.put("editTime", rs.getTimestamp("edit_time"));
                } catch (SQLDataException e) {
                    // 如果获取Timestamp失败，尝试作为Long获取并转换
                    try {
                        long addTimeLong = rs.getLong("add_time");
                        material.put("addTime", new java.sql.Timestamp(addTimeLong));
                        
                        long editTimeLong = rs.getLong("edit_time");
                        material.put("editTime", new java.sql.Timestamp(editTimeLong));
                    } catch (SQLException e2) {
                        logger.warn("无法获取或转换时间字段，使用当前时间代替: {}", e2.getMessage());
                        material.put("addTime", new java.sql.Timestamp(System.currentTimeMillis()));
                        material.put("editTime", new java.sql.Timestamp(System.currentTimeMillis()));
                    }
                }
                material.put("status", rs.getInt("status"));

                materials.add(material);
                count++;
                // 打印每个查询到的素材的基本信息
                logger.info("查询到素材 - ID: {}, 标题: {}", 
                           material.get("id"), material.get("title"));
            }
            
            logger.info("素材查询完成，共查询到 {} 个素材", count);
        } catch (Exception e) {
            logger.error("查询素材列表异常: {}", e.getMessage(), e);
            // 分析异常类型，提供更具体的错误信息
            if (e instanceof SQLException) {
                String sqlState = ((SQLException) e).getSQLState();
                int errorCode = ((SQLException) e).getErrorCode();
                logger.error("SQL异常详细信息 - SQLState: {}, ErrorCode: {}", sqlState, errorCode);
                
                // 常见SQL异常处理
                if (e.getMessage().toLowerCase().contains("connection")) {
                    logger.error("数据库连接异常，建议检查网络连接和数据库服务器状态");
                } else if (e.getMessage().toLowerCase().contains("table")) {
                    logger.error("表不存在或权限不足，请检查数据库结构和权限配置");
                }
            }
            // 即使查询失败，也返回空列表
        } finally {
            logger.debug("开始关闭数据库资源");
            if (rs != null) {
                try {
                    rs.close();
                    logger.debug("ResultSet已关闭");
                } catch (SQLException e) {
                    logger.error("关闭ResultSet失败", e);
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                    logger.debug("PreparedStatement已关闭");
                } catch (SQLException e) {
                    logger.error("关闭PreparedStatement失败", e);
                }
            }
            DatabaseUtil.closeConnection(conn);
            logger.debug("数据库连接已关闭");
        }
        return materials;
    }
}